package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;

import util.JDBCUtil;
import domain.ForumPost;
import ex.DatabaseException;

public class ForumPostDAO {
	private Connection conn;
	private PreparedStatement stmt;
	private ResultSet rs;
	
	private static final String SELECT_FORUM_POST_BY_PID="select * from forum_post where pid= ?";
	private static final String SELECT_FORUM_POSTS_BY_TID="select * from forum_post where tid= ?";
	private static final String INSERT_FORUM_POST_BY_TID="insert into forum_post (tid,uid,content,flag) values (?,?,?,?)";
	private static final String DELETE_FORUM_POST_BY_PID="delete from forum_post where pid = ?";
	private static final String UPDATE_FORUM_POST="update forum_post set content = ?, flag = 1 where pid = ?";

	//Selection of posts from ForumPost table using Post ID
	public ForumPost selectForumPostByPid(long pid) throws DatabaseException{
		conn = JDBCUtil.getConnection();
		ForumPost fp = null;
		try{
			stmt = conn.prepareStatement(SELECT_FORUM_POST_BY_PID);
			stmt.setLong(1, pid);
			rs = stmt.executeQuery();
			if (rs.next()) {
				fp = new ForumPost(rs.getLong("pid"), rs.getLong("tid"), rs.getLong("uid"), rs.getString("content"), rs.getTimestamp("date"), rs.getInt("flag"));
			}
			stmt.close();
			JDBCUtil.closeConnection(conn);		
		}catch (SQLException e) {
			throw new DatabaseException("Problem with retrieving forum post by pid! "+ e.getMessage());
		}	
		return fp;
	}
	
	public LinkedList<ForumPost> selectForumPostByTid(long tid) throws DatabaseException{
		conn = JDBCUtil.getConnection();
		LinkedList<ForumPost> list = new LinkedList<ForumPost>();
		ForumPost fp = null;
		try{
			stmt = conn.prepareStatement(SELECT_FORUM_POSTS_BY_TID);
			stmt.setLong(1, tid);
			rs = stmt.executeQuery();
			while (rs.next()) {
				fp = new ForumPost(rs.getLong("pid"), rs.getLong("tid"), rs.getLong("uid"), rs.getString("content"), rs.getTimestamp("date"), rs.getInt("flag"));
				list.add(fp);
			}
			stmt.close();
			JDBCUtil.closeConnection(conn);		
		}catch (SQLException e) {
			throw new DatabaseException("Problem with retrieving user forum post by tid! "+ e.getMessage());
		}	
		return list;
	}
	
	//Insertion of a new Post to the database
	public void insertForumPostByTid(ForumPost fp) throws DatabaseException{
		conn = JDBCUtil.getConnection();
		try {
			stmt = conn.prepareStatement(INSERT_FORUM_POST_BY_TID);
			stmt.setLong(1, fp.getTid());
			stmt.setLong(2, fp.getUid());
			stmt.setString(3, fp.getContent());
			stmt.setInt(4, fp.getFlag());
			stmt.execute();
			stmt.close();
			JDBCUtil.closeConnection(conn);
		}catch (SQLException e) {
			throw new DatabaseException("Problem with inserting forum post! "	+ e.getMessage());
		}				
	}
	
	//Removal of a Post from the database
	public void deleteForumPost(long pid) throws DatabaseException{
		conn = JDBCUtil.getConnection();
		try {
			stmt = conn.prepareStatement(DELETE_FORUM_POST_BY_PID);
			stmt.setLong(1, pid);
			stmt.execute();
			stmt.close();
			JDBCUtil.closeConnection(conn);
		}catch (SQLException e) {
			throw new DatabaseException("Problem with deleting forum post! "	+ e.getMessage());
		}				
	}
	
	//Editing the ForumPost fields
	public void updateForumPost (ForumPost fp) throws DatabaseException{
		conn = JDBCUtil.getConnection();
		try {
			stmt = conn.prepareStatement(UPDATE_FORUM_POST);
			stmt.setString(1, fp.getContent());
			stmt.setLong(2, fp.getPid());
			stmt.execute();
			stmt.close();
			JDBCUtil.closeConnection(conn);
		}catch (SQLException e) {
			throw new DatabaseException("Problem with editing forum post! "	+ e.getMessage());
		}				
	}
	
}
